Release notes

Issue: The logic error is that the value of the denominator for (PTO Alloc % for YTD) occurs at the beginning of the formula when the division calculation is 4 embedded IF-THEN conditions. If the denominator is zero, but the logic doesn't result in the associated calculation, then the result is erroneously zero and will not recognize any PTO configurations from the Labor Override driver.

Resolution: Corrected by updating the test for IF YTD PTO=0 to the correct section of the formula.

Issue: In the Staffing tab, the total hours for each month on row 1 of the Add New Jobcode calc method includes misplaced parentheses that causes the calculated hours for each pay type row to be less than they should be. This impacts variable job codes, as the total hours for the budget months do not tie to the annual paid hours section of each job code for the Staffing tab. This leads to understated hours on the Expense tab.

Resolution: In the Add New JobCode Block calc method, corrected by adjusting the formula so that the FTEs by row are no longer being reduced by the formula error within the Hours columns.

Issue: In 2019.3 when adding new providers to a plan file, users can no longer adjust Gross Revenue (no blue input cell) for the current year in the Manager Input column, which was an available functionality several versions ago. The logic in this field is similar to the Existing Provider "Manager Input logic" where it uses the (YTD Gross Charges / YTD of the chosen driver from the drop-down) multiplies by the Manager Input value of the chosen driver to calculate your gross charge adjustment. While this works in one case for existing providers, its problematic as it does not allow for additional adjustment on existing or new providers (they have zero YTD).

Resolution: Corrected the Revenue calc method by reinstating the same update as the original calc methods. Changes were made to the cells in columns L, FK, and GE for the one line Revenue calc method.

Issue: In the budget workbook, users can include the current year's budget detail information from the last completed budget. In the 2019.3 version, however, the system brings over the budget amounts but not the description for RedcordIDDesc and BudComments.

Resolution: Corrected by adding fields for the two prior years (RecordIDDesc and BudComments) in the field definition row so that the text comments entered from prior budget year will be included in the plan file.

Issue: With the changes in 2019.3 for plan files, an issue occurs when you roll up job codes together using the KHABGTcode column. The system picks up the description of one of the higher-numbered job codes since now the system uses an AQ to bring in that data versus a using GetData.

Resolution: Corrected by updating the formula for the description to use Jobcode.KHABgtCode.Description.

Issue: With the separation of the ProviderDetail and ProviderSummary logic (starting in 2019.3), the stat referencing formulas on the first line of the PComp block on the ProviderComp tab for CYB, YTD, and Remaining Projected were not correctly updated . If you elect to use a stat that is not the driving stat, the system correctly picks it up for the budget months, but not CYB, YTD , and Remaining Projected.

Resolution: Corrected by adjusting the calc method library formulas to identify driver setup for ProviderDetail or ProviderSummary and bring in the correct stat value if configured for a non-driver statistic. (ie...WRVUs may be the driver stat but provider comp is using Encounters).

Issue: In cell T87 on Expense tab, the description defaults to Calendar Days because of a cell formula error that looks to a blank cell on the Stat_Rev tab.

Resolution: Corrected by updating the formula so that "Total Key Statistic" is the description when dept statistics are used.

Issue: In the Labor Rates driver, the Job Class Exceptions is not compliant with the Jobcode.JobClass character width of 50. In the Labor Rates Assumptions table, column O is set for a​ 25-character width, thus not saving if the Job Class selected within the drop-down is more than 25 characters. As a result,​ the system displays an error message, and the driver will not save.

Resolution: Corrected by updating the string from 25 to 50 in column JC in Labor Rates.

Issue: Transform 2 should be modified n the Global Expense import because it currently concatenates department and account using the + string concatenation. This is not as flexible as CONCAT and sometimes returns errors if the source data for ACCT and DEPT are large integers. Using CONCAT converts every value to a string before concatenation.

Resolution: Corrected by changing the transform step 4 to the recommended CONCAT function.

Issue: The provider driver department description is len(50), while the department dimension description is len(100). The difference causes a save error and prevents the driver from saving.

Resolution: Corrected by increasing the description field length to 100.

Issue: The definition in Column S is Dept.KHABgtMap.Description, but row 46 description displays "Account Description" when it should display "Description".

Resolution: Corrected by changing "Account Description" to "Description".

Issue: When bringing in the YTD volume for the provider, the Field Definition Row is set to pull in YTD and this can change during the budget process if a second save needs to occur due to volume changes. Cell N27 should be referencing the Variables tab and cell 11 for the Budget YTD period.

Resolution: When bringing in the YTD volume for the provider, the Field Definition Row is set to pull in YTD and this can change during the budget process if a second save needs to occur due to volume changes. Cell N27 now references the Variables tab and cell 11 for the Budget YTD period. This replaces using the system YTD, which can change over the budget period. The second issue was corrected for the Rem Volume column by deleting the formula in O25 so that is uses the calculation in O26 instead, which takes the total minus YTD to get the projection versus using ProjCalc.

Issue: The table variable in transform 3 of the Driver-Depreciation import is {template}; it should be {temptable}.

Resolution: Corrected by updating the transform step to temptable.

Issue: The new data validation for Jobcode and Paytype do not allow for NotUsed as an option unless the driver is unprotected and it is manually entered. This is problematic because all the Hide Row logic in the plan file is looking for NotUsed in the Paytype column to determine if a row should be Visible or Hidden. This would not be an issue for a client with a rolled over File Group that had NotUsed previously listed, but any new system or changes would be problematic as most clients do not have the access to overwrite like that.

Resolution: Corrected by updating the picklists to select 'NotUsed' as an option.

Issue: The new format of Labor Config no longer includes a description for the Lump Sum Payout line. As a result, it now displays in the plan file with a zero instead of a description.

Resolution: Corrected by updating cell Q83 to be an input cell in the Labor Configuration tab.

Issue: The logic to default a newly inserted budget group to the values in Globals was missing. This was a feature added in 2019.3, which included cell logic that if the current budget group row was blank, then use Global. The driver was inadvertently reverted to a version where this logic was not present.

Resolution: Corrected by restoring the driver and calc method library from the correct version.

Issue: The Budget Labor Configuration driver does not correctly calculate the Global Hours in Period when Global Hours Per FTE Convention is "Default" AND the year is a Leap Year.

Resolution: Corrected by updating multiple formulas in the Budget Labor Configuration driver and the Budget Group calc method.

Issue: When refreshing the Budget Statistics Driver, AQ9 displays a warning that the number of records retrieved exceeds the maximum allowed, even when only one budget group was selected.

Resolution: Corrected by adjusting the data filter for AQ9 to consider the budget group selected in the refresh variable.

Issue: The system displays a data conversion error when refreshing the Budget Statistics driver (AQ9) due to large decimal values in the ChgPct column in the table.

Resolution: Corrected by updating the formula in column AS to include round logic out to 10 decimals. This will prevent the large integer errors caused by division issues of very small values.

Issue: The system does not save all changes to the Provider List.

Resolution: Corrected by updating the save logic on the affected rows.

Issue: With the move to 2019.3, the Paytype field for the Prod and Non Prod categories in the Labor Config Driver changed from an input field to a GetDataElement. The issue that shows up is the description for the paytype listed may belong a child pay type of it.

Resolution: Corrected by concatenating PAYTYPE.JobCode=PAYTYPE.Paytype to all four data sources ,which now brings in the correct description for the category.

Issue: The feature PayrollGLMapping is located in both the Budget System Maintenance and Budget Reporting section of the Bud Admin task pane.

Resolution: Corrected by removing the duplicate from the Budget System Maintenance section.

Issue: When using CYB_FTE as the comparison on Jobcode, the new summary section of the Jobcode does not contain the logic to include CYB_FTE.

Resolution: After identifying that column K calculated "FTE Comparison to YTD", which was derived from the Labor Config driver setting for JobCode and Employee labor tabs, corrected by adjusting the sum logic in total sections for JobCode summary blocks to identify values in details section. The Summary sections utilize logic to retrieve sums of details instead of retrieving values directly from the table with the setup of Axiom UDF ReapplyCurrentViews.

Issue: On the Staffing tab, when employing the use of KHABGTMap depts, dollar only pay types on these job code blocks may not show the correct dept (shows KHABGTCode Dept), thus preventing the flow of the data to the Expense tab if the account happens to be different than the home department. This may create an out of balance condition in the workbook between the Expense and Staffing tab as well as inaccurate financial reporting.

Resolution: Corrected by adjusting the calc method library formulas in columns CK and CL to look to the dept used for that specific job code versus the host department.

Issues:

1. On the Jobcode and ProviderComp tabs, YTD Retirement Calculation for salary for the prior year was understated due to not including the per/FTE component.
2. On the Jobcode and ProviderComp tabs, the retirement calculation for month 1 of the calendar year may not calculate due to comparing erroneously to YTD of prior year.

Resolution:

1. In the Jobcode and Provider calc methods, corrected by updating the formulas in column BG and LineRef Jobcode 29 to divide by the FTEs to convert the formula to salary per FTE.
2. In the Jobcode and Provider calc methods, corrected by adding logic to test for month 1 and apply the retirement limits to just that month.

Issue: In 2019.3, the Contract Provider lines of the Provider template > ProviderComp tab do not flow to the Expense tab for the corresponding dollars and hours accounts listed in columns CK and CL. These lines flow to the ProviderCompData tab for account zero, which is not picked up on the Expense tab.

Resolution: Corrected by implementing logic in the Provider template > ProviderComp tab similar to other sections in order to flow data from the static Contract Provider line items to the Expense tab for Acct and HrsAcct.

Issue: When moving to 2019.3, we changed the formula logic in the Projected Dollars column of the Jobcode Block on the Jobcode tab to factor in the rate for FTEs that are Program Additions or Position Changes. There is a missing component in this formula on the OT row which could lead to understating projected dollars if you add FTEs or overstate if you remove FTEs because the rate used does not factor in the 1.5 times the rate used on the Program Addition and Position Change lines.

Resolution: Corrected so that when removing FTEs from the productive and non-productive rows, each row will refer to it's own rate. For example, if backing out 1.0 FTEs and rows like overtime were used, the dollars will be backed out at the overtime rate as defined by the percentage used in column F of the jobcode block for that row. This update will include any rates used in the Program Additions and Program Changes rows.

Issue: The PayrollGLMapping does not work correctly due to an formula issue in column DK.

Resolution: Corrected by updating the formulas for row range extension to 2500 (alleviating concerns of clients with workbooks that exceed this row count, causing the formula to once again break) in the Employee calc method.

Issue: When adding a new job code block to a plan file, the user saves, closes, and upon re-opening the plan file, the new job code is listed twice. The issue was related to the new job code being added below the insert new job code command row. The newly added job code erroneously became a persistent part of the tab.

Resolution: By moving the insert command to the same row and the insert prompt, this updated the range so that newly added job codes were within the rebuildable part of the labor tab.

Issue: With the changes in 2019.3, we modified our lookup logic for pulling GL account information on the Jobcode tab. If clients do not use the PayrollGLMapping table or Paytype table for GL Accounts, when a job code is added manually on the Jobcode tab or by setting the NYBKHA in the ActPay27 table to 1 to interface in, it does not bring in the account from the Jobcode table, thus dollars and hours cannot flow to the Expense tab.

Resolution: Corrected by incorporated RelatedColumnValues for the JobCode block calc method to populate the additional required fields. These column values are now resident in the Jobcode calc method so that the lookup to the Jobcode.StdHours, Jobcode.HRAcct and Jobcode.GLAcct are established.

Issue: With the changes made in moving to 2019.3, AQ1 on the ProviderSummary tab is hardcoded to Off. As a result, the system will not bring in the current settings from the Provider Config Driver.

Resolution: Corrected by replicating ProviderDetail!AQ1 in ProviderSummary!AQ1 since they used the same parameters.

Issue: On the Stat_Rev tab, when selecting History as the spread method for an account, the formulas for columns Z:AK, may not spread to all months. The formula references the Configuration tab to look for periods after the YTD period used would refer to months form the current year. These values should refer to the prior year.

Resolution: Corrected the formula in cells AZ5:BK5 in the Configuration tab to use prior year periods when later than the YTD period used.

Issue: In 2019.4, the logic to show the proper pay type for Global Benefit lines on the ProviderComp tab may not return the expected paytpes due to a missing parameter in one of the AQs.

Resolution: Corrected by updating the related AQ on the Drivers tab of the plan file.

Issue: When using Global Benefit rows from the Provider List driver, the values would not spread correctly in rows 2 thru 5 of global benefit rows on the ProviderComp tab. The issue was related to a range reference that was not locked to the needed row.

Resolution: Corrected by making formula update to rows two thru five of the global benefit section.

Issue: When working on the ProviderComp tab and everything is in summary view, no totals for the FTEs or hours display - even after saving.

Resolution: Corrected by doing the following:

• Mirrored the "Total" tags identified in column CP in new column location (column CJ) for the template and Provider block calc methods.
• Adjusted the logic for Hours, FTEs, and FICA to reference column CJ instead of column CP to display on the summary section of the calc method.

Issue: A user adds a new provider to the PROVIDER table and drivers, and then inserts the Add New ProviderSummary calc method on ProviderSummary for the provider. The user then adds data and saves the plan file. The next time the plan file is opened, the provider block displays twice, resulting in recalculation errors due to duplicate keys.

Resolution: ProviderSummary uses a single calc methods for interfaced and add new. Persistent plan file requires key for add new once the record is saved to the db. If key does not exist, insert/update AQ will populate record found in table and persistent record with other save rows produce a save error. Incorporated formula to derive keys for AQ7.

Issue: In the Employee tab, a row displays in each job code block right above “Click here to insert new paytype”, causing a defect in the JobCode calc method.

Resolution: Corrected by changing the formula in the row 24 of the job code block of the Employee tab that was referencing the same key filed causing the duplicate.

Issue: When using the Fixed Adjustment rows of the self balancing option, the row reference was incorrect resulting in the adjustment not being recognized.

Resolution: This issues resolved when resolving an issue with Add New jobcode which corrected the last row range reference.

Issue: When opening the Initiatives tab and inserting New Patient Revenue calc method, the Patient Revenue block is duplicated after recalculating or refreshing the plan file. What was occurring was that one of the rows in the clac method referenced a key save back row two rows above causing the duplicate.

Resolution: Corrected by identifying AQ4 for patient-related calc methods, column BQ required key reference adjustments to the same line.

Issue: When a user is a member of Budget Hide Salaries and navigates to the Jobcode sheet, the start rates are hidden when scrolling to column BU and BV.

Resolution: Corrected by implementing the proposed [hidecolumn] logic similar to formulas identified in column G for multiple views. Determination for role based views located on Variables tab.

Issue: In Budget Deductions utility (Reports Library\Budgeting Utilities\Balance Sheet & Deductions\Budget Deductions), on the Total Patient Days - Globals row (row 116 in template), there is a formula reference to the incorrect month in the monthly columns AL:AW.

Resolution: Corrected the formula Budget_Deductions!AL116 and copied it to Budget_Deductions!AM116:AW116.

Issue: PFB-07466 - Budget Salary Rate Analysis Report [TFS 34926]

Symptom: The issue is that the filter of Acct.FSSummary=’E_Salaries’ can be too broad and may include Benefits there by​ skewing the salary rate. The AQ2 filter needs to be updated to include a filter for Acct.FSDetail='E_Salaries'.

Resolution: Columns T, V, X, and Z included a filter and column reference change to use FSDetail instead of FSSummary.

Issue: PFB-08313 - Dept Monthly Package - Scorecard - OT Hours not filtered to selected Dept [TFS 41853]

Symptom: The Overtime Hours Graph does not change when new departments are selected.

Resolution: Corrected by updating cells AD68 and AF68 and applying the dept filter to the AQs that are used in populating the graph data and the report data.

Issue: PFB-08322 - Executive Monthly Package - Dept _Variance tab

Symptom: In the Executive Monthly Package > Dept_Variance tab, columns T and X bring in Bud2020.YTDx. Column X should bring in Bud2020.Tot, and cell X9 should be '.Tot'.

Resolution: Corrected by updating the formula in X3 on the Dept_Variance tab for the Annual Budget column to use .TOT v .YTD

Issue: PFB-08393 - Cell format of FTEs on BudVar sheet of Dept Monthly Package [TFS 42163]

Symptom: In Dept Monthly Package > BudVar tab, the cell format for FTEs by GL Class should be two decimal places to match other sections of FTEs. Specifically, the following cells:

• Q100:Q103
• S100:S103
• V100:V103

Resolution: Corrected the formatting in cells the cells suggested above. No other sections needed these formatting changes.

Issue: PFB-08313 - Dept Monthly Package - Scorecard - OT Hours not filtered to selected Dept [TFS 41853]

Symptom: The Overtime Hours Graph does not change when new departments are selected.

Resolution: Corrected by updating cells AD68 and AF68 and applying the dept filter to the AQs that are used in populating the graph data and the report data.

Issue: PFB-08322 - Executive Monthly Package - Dept _Variance tab [TFS 41860]

Symptom: In the Executive Monthly Package > Dept_Variance tab, columns T and X bring in Bud2020.YTDx. Column X should bring in Bud2020.Tot, and cell X9 should be '.Tot'.

Resolution: Corrected by updating the formula in X3 on the Dept_Variance tab for the Annual Budget column to use .TOT v .YTD.

Issue: PFB-08393 - Cell format of FTEs on BudVar sheet of Dept Monthly Package [TFS 42163]

Symptom: In Dept Monthly Package > BudVar tab, the cell format for FTEs by GL Class should be two decimal places to match other sections of FTEs. Specifically, the following cells:

• Q100:Q103
• S100:S103
• V100:V103

Resolution: Corrected the formatting in cells the cells suggested above. No other sections needed these formatting changes.

Issue: Dept Monthly Package: VarAlert tab contents are not being output correctly when file processed [TFS 42198]

Symptom: The InvoiceNo column in the GL_TRANSACTIONS_20xx tables has a max string length of 30. Some invoice numbers may exceed this length, so the length should be increased to at least 50.

Resolution: Corrected by updating the max string length to 50.

Issue: Dept Monthly Package_BudVar_Formatting of PerUnit Inconsistent [TFS 42908]

Symptom: In the Dept Monthly Package, the Calendar Days do not populate and there is inconsistent per unit formatting.

Resolution: Corrected by doing the following:

• Updated all per unit columns to two decimals.
• For calendar days as stat, hide row logic was added if calendar days are zero. The default is to express calendar days as zero if the dept is using an existing statistic.

Issue: PFB-07575 - Productivity Reports - Productivity Performance-Dollars and Hours (2 reports) [TFS 37080]

Symptom: #ERR conflict existed with the Cost Management period did not resolve correctly.

Resolution: Error trapped for this issue so period selection does not cause #ERR

Issue: If changing the calc method in the Dept Other Pay section of the Jobcode tab, the changed calc methods reverts to original calc method upon rebuild of a plan file.

Resolution: Identified that the change calc method rules require a value or formula in a related cell to mitigate a replacement calc method with an empty cell. An empty cell from the replacement calc method will override the former. Corrected by incorporating formulas to derive key values in each dollars AQ.